﻿//#define debug

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Configuration;
using System.Xml.Linq;
using System.Data;
using System.Security.AccessControl;
using System.ServiceProcess;
using System.Globalization;
using System.Threading;
using Microsoft.Win32;


namespace Utilities
{
    public class Utilities
    {
        public static void SetCurrentCulture()
        {
            CultureInfo current = (CultureInfo)CultureInfo.CurrentCulture.Clone();
            current.DateTimeFormat.ShortDatePattern = "dd/MM/yyyy";
            Thread.CurrentThread.CurrentCulture = current;
        }

        public static string GetInstallationFolder()
        {
            RegistryKey HKLM = Registry.LocalMachine;
            RegistryKey RegKey = HKLM.OpenSubKey("SOFTWARE\\Mynte");
            string InstallPath = string.Empty;

            if (RegKey == null)
            { }
            else
                try
                {

                    InstallPath = RegKey.GetValue("InstallationFolder").ToString();
                }
                catch (Exception ex)
                { }
            return InstallPath;
        }

    }
    public class SQLite
    {

        static string[] SQLiteReservedKeywords = { "ABORT", "ACTION", "ADD", "AFTER", "ALL", "ALTER", "ANALYZE", "AND", "AS", "ASC", "ATTACH", "AUTOINCREMENT", "BEFORE", "BEGIN", "BETWEEN", "BY", "CASCADE", "CASE", "CAST", "CHECK", "COLLATE", "COLUMN", "COMMIT", "CONFLICT", "CONSTRAINT", "CREATE", "CROSS", "CURRENT_DATE", "CURRENT_TIME", "CURRENT_TIMESTAMP", "DATABASE", "DEFAULT", "DEFERRABLE", "DEFERRED", "DELETE", "DESC", "DETACH", "DISTINCT", "DROP", "EACH", "ELSE", "END", "ESCAPE", "EXCEPT", "EXCLUSIVE", "EXISTS", "EXPLAIN", "FAIL", "FOR", "FOREIGN", "FROM", "FULL", "GLOB", "GROUP", "HAVING", "IF", "IGNORE", "IMMEDIATE", "IN", "INDEX", "INDEXED", "INITIALLY", "INNER", "INSERT", "INSTEAD", "INTERSECT", "INTO", "IS", "ISNULL", "JOIN", "KEY", "LEFT", "LIKE", "LIMIT", "MATCH", "NATURAL", "NO", "NOT", "NOTNULL", "NULL", "OF", "OFFSET", "ON", "OR", "ORDER", "OUTER", "PLAN", "PRAGMA", "PRIMARY", "QUERY", "RAISE", "REFERENCES", "REGEXP", "REINDEX", "RELEASE", "RENAME", "REPLACE", "RESTRICT", "RIGHT", "ROLLBACK", "ROW", "SAVEPOINT", "SELECT", "SET", "TABLE", "TEMP", "TEMPORARY", "THEN", "TO", "TRANSACTION", "TRIGGER", "UNION", "UNIQUE", "UPDATE", "USING", "VACUUM", "VALUES", "VIEW", "VIRTUAL", "WHEN", "WHERE" };

        /// <summary>
        /// Crea il database per il servizio in %ProgramFiles% e replica in esso le tabelle del DataSet del WMI
        /// </summary>
        /// <param name="ds">DataSet di origine</param>
        public static void CreateTables(DataSet ds)
        {
            
            try
            {
                String ProgramFolder = Utilities.GetInstallationFolder();

                if (!System.IO.File.Exists(String.Format(@"{0}\{1}.db", ProgramFolder, "Mynte")))
                {
                    SQLiteConnection.CreateFile(String.Format(@"{0}\{1}.db", ProgramFolder, "Mynte"));

                    FileSecurity fileSecurity = System.IO.File.GetAccessControl(String.Format(@"{0}\{1}.db", ProgramFolder, "Mynte"));
                    fileSecurity.AddAccessRule(new FileSystemAccessRule("Everyone", FileSystemRights.Read, AccessControlType.Allow));
                    fileSecurity.AddAccessRule(new FileSystemAccessRule("Everyone",FileSystemRights.Write, AccessControlType.Allow));

                    System.IO.File.SetAccessControl(String.Format(@"{0}\{1}.db", ProgramFolder, "Mynte"), fileSecurity); // Set the new access settings.
                    

                    //Configuration asd = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
                    //asd.ConnectionStrings.ConnectionStrings.Remove("SQLite");
                    //asd.ConnectionStrings.ConnectionStrings.Add(new ConnectionStringSettings("SQLite", String.Format(@"Data Source={0}\{1}.db; Version=3;", System.Environment.ExpandEnvironmentVariables("%ALLUSERSPROFILE%"), System.Environment.MachineName)));
                    //asd.Save(ConfigurationSaveMode.Modified);
                    //ConfigurationManager.RefreshSection("connectionStrings");
                }
            }
            catch (Exception ex)
            {
#if debug
                System.IO.File.AppendAllText(Utilities.GetInstallationFolder() + "Log.txt", "Create DB " + ex.Message + "\r\n"); 
#endif
            }
            // Carico lo schema del dataset e salvo in un dizionario i nomi delle tabelle e i nomi delle colonne
            //SQLiteConnection conn = new SQLiteConnection(ConfigurationManager.ConnectionStrings["SQLite"].ConnectionString.ToString());
            XDocument DBScherma = XDocument.Parse(ds.GetXmlSchema());
            IEnumerable<XElement> schema = from node in DBScherma.DescendantNodes()
                                           where (node as XElement).Name.LocalName.Equals("element")
                                           select node as XElement ;
            String DataBaseName = schema.ElementAt(0).FirstAttribute.Value.ToString();
            Dictionary<String, object[]> Tables = new Dictionary<string, object[]>();

            foreach (XNode node in schema)
            {
                XElement CurrentElement = node as XElement;
                if (CurrentElement.HasElements &&  !CurrentElement.FirstAttribute.Value.ToString().Equals(DataBaseName))
                    Tables.Add(CurrentElement.FirstAttribute.Value, (CurrentElement.Descendants(CurrentElement.Name.Namespace+"element")).ToArray());       // Recupero tutti i discendenti dei nodi con elementi (Nome colonne della tabella)
            }

            List<string> Query = new List<string>();
            foreach (KeyValuePair<string,object[]> kvp in Tables)
            {
                String CreateQuery = string.Empty;
                CreateQuery += String.Format("CREATE TABLE IF NOT EXISTS {0} (",kvp.Key);
                foreach (object item in kvp.Value)
                {
                    string[] FieldName = {(item as XElement).FirstAttribute.Value.ToUpper()};
                    if ((item as XElement).FirstAttribute.Value.Equals("ID"))
                        CreateQuery += String.Format("{0} INTEGER\r\n,", (item as XElement).FirstAttribute.Value);
                    else
                        CreateQuery += String.Format("{0} VARCHAR\r\n,", SQLiteReservedKeywords.Intersect(FieldName).Count() > 0 ? "is" + (item as XElement).FirstAttribute.Value : (item as XElement).FirstAttribute.Value);
                }

                CreateQuery += "PRIMARY KEY(ID, InsertDate)  ON CONFLICT REPLACE";
                CreateQuery += ");\r\n";
                Query.Add(CreateQuery);
            }

            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(Environment.ExpandEnvironmentVariables(ConfigurationManager.ConnectionStrings["SQLite"].ConnectionString)))
                {
                    conn.Open();
                    using (SQLiteCommand comm = new SQLiteCommand(conn))
                    {
                        foreach (String query in Query)
                        {
                            comm.CommandText = query;
                            comm.ExecuteNonQuery();
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                #if debug
	                System.IO.File.AppendAllText(Utilities.GetInstallationFolder() + "Log.txt", "Adsd" + ex.Message +  "\r\n");
                #endif
            }
        }

        public static void InsertData(DataSet ds)
        {
            String InsertQuery = String.Empty;
            try
            {
                foreach (DataTable Table in ds.Tables)
                {
                    foreach (DataRow Row in Table.Rows)
                    {
                        InsertQuery += String.Format("INSERT INTO {0} VALUES (", Table.TableName);
                        for (int i = 0; i < Table.Columns.Count; i++)
                        {
                            InsertQuery += String.Format("'{0}', ", Row[i].ToString().Replace('\'', ' ').Replace("(", " ").Replace(")", " "));
                        }
                        InsertQuery = InsertQuery.Remove(InsertQuery.Length - 2);
                        InsertQuery += ");\r\n";
                    }
                }

                using (SQLiteConnection conn = new SQLiteConnection(Environment.ExpandEnvironmentVariables(ConfigurationManager.ConnectionStrings["SQLite"].ConnectionString)))
                {
                    conn.Open();
                    using (SQLiteCommand comm = new SQLiteCommand(InsertQuery, conn))
                    {
                        comm.ExecuteNonQuery();
                    }
                }
            }
            catch (Exception ex)
            {

                #if debug
		            System.IO.File.AppendAllText(Utilities.GetInstallationFolder() + "Log.txt","Normal"+ ex.Message + InsertQuery + "\r\n");
                #endif
            }

        }

        public static void InsertException(string message)
        {
            String CommandText = @"CREATE TABLE IF NOT EXISTS Log( MachineName VARCHAR NOT NULL
							                                     , InsertDate VARCHAR NOT NULL
							                                     , Message VARCHAR NOT NULL
							                                     );";
            CommandText += string.Format("INSERT INTO Log VALUES('{0}','{1}','{2}');",Environment.MachineName,DateTime.Today.ToShortDateString(),message);
            try
            {
                using (SQLiteConnection conn = new SQLiteConnection(Environment.ExpandEnvironmentVariables(ConfigurationManager.ConnectionStrings["SQLite"].ConnectionString)))
                {
                    conn.Open();
                    using (SQLiteCommand comm = new SQLiteCommand(CommandText, conn))
                    {
                        comm.ExecuteNonQuery();
                    }
                }

            }
            catch (Exception ex)
            {
                #if debug
		            System.IO.File.AppendAllText(Utilities.GetInstallationFolder() + "Log.txt", "Error exception " + ex.Message + CommandText + "\r\n");
	            #endif            
            }
        }
        
    }
}
